/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ttt.nor2.importdata.txt;

import com.ttt.nor2.config.RtnBilling;
import com.ttt.nor2.util.ExceptionUtil;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpStatus;
import org.apache.commons.httpclient.methods.PostMethod;
import org.apache.commons.httpclient.methods.RequestEntity;
import org.apache.commons.httpclient.methods.StringRequestEntity;
import org.apache.log4j.Logger;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;

/**
 *
 * @author Administrator
 */
public abstract class ImportFile{

    protected String[] rent_month;
    protected Connection conn;
    protected String line;
    protected int row = 1;
    protected RtnBilling rtn = new RtnBilling();
    protected boolean ffile;
    private String split;
    private Logger logger = Logger.getLogger(ImportFile.class);
    private Integer arrMonth[] = new Integer[12];
    private PreparedStatement pstmt;
    private ResultSet rs;

    public boolean isFfile() {
        return ffile;
    }

    public void setFfile(boolean ffile) {
        this.ffile = ffile;
    }

    public RtnBilling getRtn() {
        return rtn;
    }

    public void setRtn(RtnBilling rtn) {
        this.rtn = rtn;
    }

    public int getRow() {
        return row;
    }

    public void setRow(int row) {
        this.row = row;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public ImportFile() {
    }

    public String getLine() {
        return line;
    }

    public void setLine(String line) {
        this.line = line;
    }

    public String getSplit() {
        return split;
    }

    public void setSplit(String split) {
        this.split = split;
    }

    public void interpret() {
        rent_month = line.split(split);
        if (rent_month.length == 0) {
            rent_month[0] = split;
        }
        for (int i = 0; i < rent_month.length; i++) {
            rent_month[i] = rent_month[i].trim();
            logger.debug("Value of split in array [" + i + "]:[" + rent_month[i] + "]");
        }
    }

    public void setRow1() {
        String s = rent_month[0].substring(rent_month[0].length() - 10);
        rtn.setYear(Integer.parseInt(s.substring(6)) - 543);
        rtn.setMon(Integer.parseInt(s.substring(3, 5)));
        logger.info("Year : [" + rtn.getYear() + "]mon[" + rtn.getMon() + "]");
        //month = Integer.toString(rtn.getYear()) + Integer.toString(rtn.getMon());
        if (rent_month[0].indexOf("CA") > -1) {
            rtn.setIndexMonth(7);
            logger.debug("index month = " + rtn.getIndexMonth());
            rtn.setStatus("CA");
        } else {
            rtn.setIndexMonth(6);
            rtn.setStatus("");
        }
    }

    public void setRow2() {
        int index = 0;
        for (int i = 0; i < rent_month.length; i++) {
            int index_month = rent_month[i].indexOf("Month");
            if (index_month > -1) {
                arrMonth[index] = Integer.parseInt(rent_month[i].substring(0, index_month - 1));
                index++;
            }            
        }
        rtn.setArrMonth(arrMonth);
        for (int i = 0; i < arrMonth.length; i++) {
            logger.debug("array month[" + i + "]:[" + arrMonth[i] + "]");
        }

    }


    public abstract void savedata();

    protected void mtbPhoneRent(long cust_id, String rent_id, long phone_id, long network_id) {
        try {
            String sql = "insert into phonerents(cust_id, rent_id, phone_id, network_id )values(?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, cust_id); // rent_id
            pstmt.setString(2, rent_id); // rent_id
            pstmt.setLong(3, phone_id);
            pstmt.setLong(4, network_id);
            pstmt.executeUpdate();
            pstmt.close();

        } catch (Exception e) {
            logger.error("Exception function mthpone error " + ExceptionUtil.getStackTrace(e));
        }
    }

    protected int mtbRes(String res_desc) {
        int resid = 0;
        String sql = "select res_id  from res where res_desc=? ";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, res_desc);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                resid = rs.getInt("res_id");
            } else {
                resid = (int) genkey("res_id");
                sql = "insert into res(res_id, res_desc)values(?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setLong(1, resid); // rent_id
                pstmt.setString(2, res_desc); // rent_id
                pstmt.executeUpdate();
                pstmt.close();

            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            logger.error("Exception function customer error " + ExceptionUtil.getStackTrace(e));
        }
        return resid;
    }

    protected long mtbCustomer(Hashtable ht, String name) {
        String sql = "select * from customer where cust_id=?";
        long cust_id = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, (String) ht.get("cusno"));
            rs = pstmt.executeQuery();
            if (rs.next()) {
                cust_id = rs.getLong("cust_id");

            } else {
                cust_id = Long.parseLong((String) ht.get("cusno"));
                sql = "insert into customer(cust_id, name, address, priority, status_cust)values(?, ?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, (String) ht.get("cusno"));
                pstmt.setString(2, name);
                pstmt.setString(3, (String) ht.get("address"));
                pstmt.setString(4, (String) ht.get("priority"));
                pstmt.setString(5, (String) ht.get("status"));
                pstmt.executeUpdate();
                pstmt.close();

            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            logger.error("Exception function customer error " + ExceptionUtil.getStackTrace(e));
        }
        return cust_id;

    }

    protected void mtbCustomer(long cust_id, String res_desc) {// parameter name customer
        // find data old in tb customer
        String sql = "";

        try {

            int resid = mtbRes(res_desc);
            sql = "update  customer set res_id=? where cust_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, resid);
            pstmt.setLong(2, cust_id);
            pstmt.executeUpdate();
            pstmt.close();


        } catch (SQLException e) {
            logger.error("Exception function customer error " + ExceptionUtil.getStackTrace(e));
        }

    }

    protected long mtbCustomer(String res_desc, String name) {
        // find data old in tb customer
        String sql = "";
        long cust_id = 0;


        try {
            sql = "select cust_id from customer where name = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                cust_id = rs.getLong("cust_id");

            } else {
                int resid = mtbRes(res_desc);
                cust_id = genkey("cust_id");
                sql = "insert into customer(cust_id, name, res_id)values(?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setLong(1, cust_id);
                pstmt.setString(2, name);
                pstmt.setInt(3, resid);
                pstmt.executeUpdate();
                pstmt.close();

            }
        } catch (Exception e) {
            logger.error("Exception function customer error " + ExceptionUtil.getStackTrace(e));
        }
        return cust_id;
    }

    protected long mtbPhone(String phoneno, String office_desc, String status, String province_id) {
        String sql = "";
        long phone_id = 0;
        try {
            int office_id = (int) mtOffice(province_id, office_desc);
            sql = "select phone_id from phone where phone_no=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, phoneno);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                phone_id = rs.getInt("phone_id");
                //update all

                sql = "update phone set status_phone=?, office_id=? where phone_no=?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, status); // rent_id
                pstmt.setInt(2, office_id);
                pstmt.setString(3, phoneno);
                pstmt.executeUpdate();
                pstmt.close();

            } else {
                phone_id = genkey("phone_id");
                sql = "insert into phone(phone_id, phone_no, status_phone, office_id)values(?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setLong(1, phone_id); // rent_id
                pstmt.setString(2, phoneno); // rent_id
                pstmt.setString(3, status);
                pstmt.setInt(4, office_id);
                pstmt.executeUpdate();
                pstmt.close();

            }
            rs.close();
            pstmt.close();


        } catch (Exception e) {
            logger.error("Exception function manage table phone " + ExceptionUtil.getStackTrace(e));
        }
        return phone_id;
    }
    //parameter phone_number, rent_id, province_id   

    protected int mtOffice(String province_id, String office_desc) {//parameter province_id, officeer

        String sql = "";
        int off_id = 0;
        try {
            sql = "select office_id from officer where office_desc=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, office_desc);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                off_id = rs.getInt("office_id");
                logger.debug("Check city in table office [" + off_id + "][" + office_desc + "]");
            } else {
                off_id = (int) genkey("off_id");
                sql = "insert into officer (province_id, office_desc, office_id) values(?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, province_id);
                pstmt.setString(2, office_desc);
                pstmt.setInt(3, off_id);
                pstmt.executeUpdate();
                pstmt.close();
                logger.info("insert to table office [" + province_id + "][" + office_desc + "]");

            }
            rs.close();
            pstmt.close();

            //set phone
        } catch (Exception e) {
            logger.error("Exception function manage table officer " + ExceptionUtil.getStackTrace(e));
        }
        return off_id;
    }
    //public abstract void setFileUpload(String fileUpload);

    protected long mtNetwork() {
        long network_id = 0;
        String sql = "";
        try {
            network_id = genkey("network_id");
            sql = "insert into network( network_id)values(?)";

            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, network_id);
            pstmt.executeUpdate();
            pstmt.close();

        } catch (Exception e) {
            logger.error("Exception post " + ExceptionUtil.getStackTrace(e));
        }
        return network_id;
    }

    protected long mtNetwork(String phoneno) {
        long network_id = 0;
        Hashtable ht = getDataFromCS(phoneno);
        try {
            String sql = "select network_id from network where location=? and exchname=? and prino=? and pripair=? " +
                    "and cab=? and secno=? and secpair=? and dpno=? and dppin=? and dwlen=? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, (String) ht.get("location"));
            pstmt.setString(2, (String) ht.get("exchname"));
            pstmt.setString(3, (String) ht.get("prino"));
            pstmt.setString(4, (String) ht.get("pripair"));
            pstmt.setString(5, (String) ht.get("cab"));
            pstmt.setString(6, (String) ht.get("secno"));
            pstmt.setString(7, (String) ht.get("secpair"));
            pstmt.setString(8, (String) ht.get("dpno"));
            pstmt.setString(9, (String) ht.get("dppin"));
            pstmt.setString(10, (String) ht.get("dwlen"));
            rs = pstmt.executeQuery();
            if (rs.next()) {
                network_id = rs.getInt("network_id");
            } else {
                network_id = genkey("network_id");
                sql = "insert into network(location, exchname, prino, pripair, cab, secno, secpair, dpno, dppin, dwlen, network_id)" +
                        "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, (String) ht.get("location"));
                pstmt.setString(2, (String) ht.get("exchname"));
                pstmt.setString(3, (String) ht.get("prino"));
                pstmt.setString(4, (String) ht.get("pripair"));
                pstmt.setString(5, (String) ht.get("cab"));
                pstmt.setString(6, (String) ht.get("secno"));
                pstmt.setString(7, (String) ht.get("secpair"));
                pstmt.setString(8, (String) ht.get("dpno"));
                pstmt.setString(9, (String) ht.get("dppin"));
                pstmt.setString(10, (String) ht.get("dwlen"));
                pstmt.setLong(11, network_id);
                pstmt.executeUpdate();
                pstmt.close();
            }
        } catch (Exception e) {
            network_id = genkey("network_id");
            logger.error("Exception post " + ExceptionUtil.getStackTrace(e));
        }
        return network_id;
    }

    protected Hashtable getDataFromCS(String phoneno) {
        Hashtable ht = new Hashtable();
        PostMethod post = null;
        try {
            StringBuffer data = new StringBuffer();
            data.append("tel_no=" + phoneno);
            String dataPost = data.toString();
            post = new PostMethod("http://10.30.10.3/cgi-bin/getcssdata.pl");
            logger.info("Post ot URL : http://10.30.10.3/cgi-bin/getcssdata.pl");
            RequestEntity entity = new StringRequestEntity(dataPost, "application/x-www-form-urlencoded", "TIS-620");
            post.setRequestEntity(entity);

            post.setRequestHeader("Content-type", "application/x-www-form-urlencoded");

            HttpClient httpclient = new HttpClient();
            httpclient.executeMethod(post);
            if ((post.getStatusCode() == HttpStatus.SC_OK) || (post.getStatusCode() == 202)) {
                InputStream is = post.getResponseBodyAsStream();
                SAXBuilder builder = new SAXBuilder();
                Document doc = builder.build(is);
                Element root = doc.getRootElement();
                String cusno = root.getChild("CSSDATA").getChild("CUSNO").getTextTrim();
                ht.put("cusno", cusno);
                logger.info("Customer id:[" + cusno + "]");
                String location = root.getChild("CSSDATA").getChild("NETWORK").getChild("LOCATION").getTextTrim();
                ht.put("location", location);
                logger.info("Location id:[" + location + "]");
                String exchname = root.getChild("CSSDATA").getChild("NETWORK").getChild("EXNAME").getTextTrim();
                ht.put("exchname", exchname);
                logger.info("Exchange name id:[" + exchname + "]");
                String prino = root.getChild("CSSDATA").getChild("NETWORK").getChild("PRINO").getTextTrim();
                ht.put("prino", prino);
                logger.info("PRI NO id:[" + prino + "]");
                String pripair = root.getChild("CSSDATA").getChild("NETWORK").getChild("PRIPAIR").getTextTrim();
                ht.put("pripair", pripair);
                logger.info("pripair id:[" + pripair + "]");
                String cab = root.getChild("CSSDATA").getChild("NETWORK").getChild("CAB").getTextTrim();
                ht.put("cab", cab);
                logger.info("pripair id:[" + cab + "]");
                String secno = root.getChild("CSSDATA").getChild("NETWORK").getChild("SECNO").getTextTrim();
                ht.put("secno", secno);
                logger.info("secon id:[" + secno + "]");
                String secpair = root.getChild("CSSDATA").getChild("NETWORK").getChild("SECPAIR").getTextTrim();
                ht.put("secpair", secpair);
                logger.info("secon id:[" + secno + "]");
                String dpno = root.getChild("CSSDATA").getChild("NETWORK").getChild("DPNO").getTextTrim();
                ht.put("dpno", dpno);
                logger.info("db no:[" + dpno + "]");
                String dppin = root.getChild("CSSDATA").getChild("NETWORK").getChild("DPPIN").getTextTrim();
                ht.put("dppin", dppin);
                logger.info("db no:[" + dppin + "]");
                String dwlen = root.getChild("CSSDATA").getChild("NETWORK").getChild("DWLEN").getTextTrim();
                ht.put("dwlen", dwlen);
                logger.info("dwlen no:[" + dwlen + "]");
                String name = root.getChild("CSSDATA").getChild("CUSTOMER").getChild("NAME").getTextTrim();
                ht.put("name", name);
                logger.info("name no:[" + name + "]");
                String address = root.getChild("CSSDATA").getChild("CUSTOMER").getChild("ADDRESS").getTextTrim();
                ht.put("address", address);
                logger.info("address no:[" + address + "]");
                String priority = root.getChild("CSSDATA").getChild("CUSTOMER").getChild("PRIORITY").getTextTrim();
                ht.put("priority", priority);
                logger.info("priority no:[" + priority + "]");
                String status = root.getChild("CSSDATA").getChild("CUSTOMER").getChild("STATUS").getTextTrim();
                ht.put("status", status);
                logger.info("status no:[" + status + "]");

            }//end if

        } catch (Exception e) {
            logger.error("Exception post " + ExceptionUtil.getStackTrace(e));
        } finally {
            post.releaseConnection();
        }
        return ht;
    }

    public long genkey(String key) {
        long rtnval = 0;

        try {
            String sql = "select getkey('" + key + "') as getkey";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                rtnval = rs.getInt("getkey");
                logger.debug("key id :[" + rtnval + "]");
            }
            rs.close();
            pstmt.close();
        } catch (Exception e) {
            logger.debug("Exception genkey error" + ExceptionUtil.getStackTrace(e));
        }
        return rtnval;
    }
}
